package sql;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Vector;
/**
 * This class Handles all customer order sql procedurs
 * @author Ron Shenkerman
 * @author Ivan Onyshchenko
 */
public class CustomerOrders {
	

	/**
	 * 
	 * @return all customer orders
	 * @throws SQLException
	 */
	public Object  getOrdersData() throws SQLException
	{
	        Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			ResultSet rs= stmt.executeQuery("SELECT * FROM mtl.customer_orders ;");
		return rs;
				
	}
	/**
	 * 
	 * @return customer order 
	 */
	public ArrayList<String> getCusOrders() {
		ArrayList<String> arr=new ArrayList<String>();
		
		Statement stmt;
		SQLConnection conn = new  SQLConnection();
		try {
			stmt = conn.getConnection().createStatement();
		
		
		ResultSet rs= stmt.executeQuery("SELECT customer_orders.order_id,customer.name,mtl.customer_orders.start_date, mtl.customer_orders.end_date,mtl.customer_orders.price,mtl.customer_orders.status FROM mtl.customer_orders,mtl.customer where mtl.customer_orders.cust_id=mtl.customer.cust_id;");
			while (rs.next())
			{
				
				arr.add(rs.getString(1));
				arr.add(rs.getString(2));
				arr.add(rs.getString(3));
				arr.add(rs.getString(4));
				arr.add(rs.getString(5));
				arr.add(rs.getString(6));
			}
	
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
return arr;
	}
	public Object openCusOrderDB(ArrayList<String> cusOrderAdd,Vector<Vector<String>> dataCustomersOrder) 
	{
		try 
		{
			
			Vector<String> vect = new Vector<String>();
			Statement stmt;
			SQLConnection conn = new  SQLConnection();
			stmt = conn.getConnection().createStatement();
			System.out.println("is ?:"+cusOrderAdd.get(0)+"");

			ResultSet rs= stmt.executeQuery("SELECT cust_id FROM mtl.customer WHERE name='"+cusOrderAdd.get(0)+"';");
			rs.next();
			String custid=rs.getString(1);
			java.util.Date dt = new java.util.Date();
			java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
			String currentTime = sdf.format(dt);
		 	stmt.executeUpdate("insert into mtl.customer_orders(cust_id,start_date,price,status) values('"+Integer.parseInt(custid)+"','"+currentTime+"','"+cusOrderAdd.get(1)+"','"+"Initialize"+"');");
		 	ResultSet rs2= stmt.executeQuery("SELECT order_id FROM mtl.customer_orders WHERE order_id = (SELECT MAX(order_id) FROM mtl.customer_orders);");
			rs2.next();
			String orderid=rs2.getString(1);
		 	int ind=0;
		 	System.out.println(dataCustomersOrder.size());
		 	System.out.println(dataCustomersOrder.size());
		 	while(ind<dataCustomersOrder.size())
		 	{
		 	vect=dataCustomersOrder.elementAt(ind);
		 	System.out.print(vect.get(1));System.out.print(vect.get(2));System.out.println(vect.get(3));
		 	String[] parts = vect.get(1).split("-");
		 	String part1 = parts[0]; 
		 	stmt.executeUpdate("insert into mtl.items_in_cust_order(order_id,iditems,quantity,price) values('"+Integer.parseInt(orderid)+"','"+Integer.parseInt(part1)+"','"+vect.get(2)+"','"+vect.get(3)+"');");
		 	ind++;
		 	
		 	}
			return 1;
		
		}
  
		catch (Exception ex )
		{
				ex.printStackTrace();
		}
		return 0;
	}
	public ArrayList<String> watchorder(String id) {
	
		ArrayList<String> arr=new ArrayList<String>();
		
		Statement stmt;
		SQLConnection conn = new  SQLConnection();
		try {
			stmt = conn.getConnection().createStatement();
		
		
		ResultSet rs= stmt.executeQuery("SELECT c.name,d.name,b.quantity,b.price FROM mtl.customer_orders a ,mtl.items_in_cust_order b , mtl.customer c , mtl.items d WHERE a.order_id =  '"+id+"' AND a.order_id = b.order_id AND a.cust_id = c.cust_id AND d.iditems = b.iditems ;");
		while (rs.next())
			{
				
				arr.add(rs.getString(1));
				arr.add(rs.getString(2));
				arr.add(rs.getString(3));
				arr.add(rs.getString(4));
				
			}
	
		}
		catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return arr;
	}
	
	public int updatecuststatusdb(String id,String status) {
		
		Statement stmt;
		SQLConnection conn = new  SQLConnection();
		try {
				stmt = conn.getConnection().createStatement();
				if(status.equals("closed"))
				{
					java.util.Date dt = new java.util.Date();
					java.text.SimpleDateFormat sdf = new java.text.SimpleDateFormat("yyyy-MM-dd");
					String currentTime = sdf.format(dt);
					stmt.executeUpdate("update mtl.customer_orders set status='"+status+"' , end_date = '"+currentTime+"' where order_id='"+id+"';");
					return 1;
				}
					else
					{
						
						stmt.executeUpdate("update mtl.customer_orders set status='"+status+"' where order_id='"+id+"';");
						return 1;
					}
				
				
			}
		catch (SQLException e) 
		{
			
			e.printStackTrace();
		}
		return 0;

	}
}
